Set up Microsoft Power Automate with Sage 200

Create a Microsoft Entra enterprise application

Note: Microsoft Entra ID was formerly known as Azure Active Directory (AAD).

First you need to create a Microsoft Entra enterprise application.

1. Go to entra.microsoft.com (opens in a new tab) and sign in using the Microsoft 365 account you used to activate your subscription and link to Sage 200.

2. Select Identity > Applications > App registrations.

3. Select New registration.

   1. Set the Name for the application, for example Sage 200 application.

   2. Leave the supported account types set to Accounts in the organizational directory only.

   3. Leave the Redirect URI blank, as you'll enter that later when you create a Custom Connector.

   4. Select Register.

      Once the application has registered, you'll see an overview with details of the application.

4. Copy the Application (client) ID to the clipboard and keep this somewhere. You'll need this later when you create the Custom Connector.

5. Select Certificates & secrets.

   1. Select New client secret.
   2. Enter a Description, such as Sage 200 client key.
   3. Set Expires to the expiry date, for example 24 months.

   4. Select Add.

      A new client secret will be displayed.

   5. Copy the Value of the new client secret to the clipboard, and save this somewhere. You'll need this later.

      Note - warning

      You must keep a note of the client secret Value now, as you will not be able to return to this page once you leave.

6. Select API permissions.

   1. Select Add a permission.

   2. Select APIs my organization uses.

   3. Select Sage 200c API from the list.

      Tip: To find this, type sage in the Search box to filter the list.

   4. In Delegated permissions, select (tick) all the permissions that are listed.

      Note:

      • You may only see one permission listed, for user_impersonation.

      • The permissions in each group may not be visible, so to display them you must open each group, or select Expand all.

   5. Select Add permissions.

Download the custom connector and flows

You will need to download the files required to set up your custom connector and flows.

1. Download the Custom connector and flows (opens in a new tab).

2. Open and unzip the *.zip file.

   This contains:

   • Custom Connectors\Sage 200.PowerApps.swagger.json

     OpenAPI (Swagger) file that you'll use to create the custom connector.

   • Purchase Requisition Email Flow\PurchaseRequisitionAuthorisationFlow.zip

     Used to set up the purchase requisitions flow (email notifications).

   • Supplier Bank Email Flow\SupplierBankDetailsFlow.zip

     Used to set up the supplier bank details flow (email notifications).

Create the Sage 200 Custom Connector

You will need to create a Custom Connector for Microsoft Power Automate to access your data in Sage 200.

To create a Custom Connector, you must have first created your Microsoft Entra enterprise application (formerly Azure Active Directory application.).

Note: If you have already created a Custom Connector for Sage 200 before the 2020 R1 release, you will need to update your Custom Connector first before you create new flows. See Update an existing Custom Connector.

Get your subscription key

To set up the Sage 200 Custom Connector you will need your subscription key (also known as your primary key for the Sage API).

Create your Custom Connector

1. Go to make.powerautomate.com (opens in a new tab) and sign in using the Microsoft 365 account you used to activate your subscription and link to Sage 200.

   Note: The instructions here will show you how to create a Custom Connector in Microsoft Power Automate, but you can also create it in Power Apps.

2. Select Data > Custom connectors.

3. Select New Custom Connector > Import an OpenAPI file.

   1. Enter the Connector name, for example Sage 200 Connector.

   2. Select Import.

   3. Select the file Sage 200.PowerApps.swagger.json that you downloaded, then select Open.

   4. Select Continue to create the connector.

4. Once the connector has been created, enter the connector's settings.

   1. On the General tab:

      1. Do not select Connect via on-premises data gateway (this is not selected by default).

      2. Set the Scheme to HTTPS.

      3. Edit the Host to read api.columbus.sage.com (by default this is s200api.columbus.sage.com).

      4. Set the Base URL to /uk/sage200/accounts/v1

   2. Move to the Security tab:

      1. Set the Authentication type to OAuth 2.0.

      2. Set the Identity provider to Azure Active Directory.

      3. Set the Client ID to the Application (client) ID for your application.

         You will have copied this earlier when you created the Azure Active Directory application.

      4. Set the Client secret to the client secret value for your application.

         You will have copied this earlier when you created the Azure Active Directory application.

      5. Set the Resource URL to https://SageCSPEU.onmicrosoft.com/6ce3bbc5-d381-4d11-b079-50accd62241a

      6. Do not change the Login URL, Tenant ID, and Scope.

   3. Select Create connector.

      It might take a minute for the connector to be created.

   4. Once the connector has been created, copy the Redirect URL to the clipboard.

      You'll need to enter this in your Microsoft Entra enterprise application.

5. Go to entra.microsoft.com (opens in a new tab) and sign in using the Microsoft 365 account you used to activate your subscription and link to Sage 200.

   1. Select Identity > Applications > App registrations.

   2. Select the application that you created.

   3. Select Authentication.

   4. Select Add a platform (in platform configurations).

      1. Select the Web application type.

      2. Set the Redirect URI to the Redirect URL that you copied from your Custom Connector.

      3. Select Configure to add the platform configuration.

6. Go back to your Custom Connector at make.powerautomate.com (opens in a new tab).

   1. Move to the Test tab.

   2. Select New Connection (in the Connections section).

   3. Select Create to create the connection.

      • You may asked to sign in with your Microsoft 365 account, and Accept the permissions requested.

        Sign in using the Microsoft 365 account you used to activate your subscription and link to Sage 200.

      • The new connection to the Custom Connector will be displayed in the Connections list.

   4. Select Custom Connectors.

   5. Select Edit (pencil icon) for your Custom Connector.

   6. Move to the Test tab.

   7. Select GetSites from the list of Operations.

   8. Set ocp-apim-subscription-key to your subscription key for the Sage API.

   9. Select Test operation.

      It might take a minute for the test to complete.

   10. Check the test Response:

       • The Status will show OK (200) if the test was successful.

       • The Body will show details of your companies in Sage 200.

         Copy the company_id and site_id for each of your companies. You'll need these details later when you set up a flow.

         Example of response with company details

         [
           {
             "company_id": 1,
             "company_name": "Company One",
             "site_id": "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxxx",
             "site_name": "YourSiteName"
           },
           {
             "company_id": 2,
             "company_name": "Company Two",
             "site_id": "xxxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxxx",
             "site_name": "YourSiteName"
           }
         ]
         

         Note:

         If the status is OK (200) but you don't see any company details in the Body, this might be because:

         • Your Sage 200 server is not running.

         • You haven't yet set up Sage 200 to connect with Microsoft 365, and assigned your Microsoft account email address with a user account in Sage 200.

   11. Select Close.

Share your Custom Connector

If other people in your organisation will also be setting up Power Automate or Power Apps, you can share your Custom Connector with them.

You can skip this step if you don't want to share the Custom Connector, or want to do this later.

Update an existing Custom Connector

If you have already created a Custom Connector for Sage 200 before the 2020 R1 release, you will need to update your Custom Connector first before you create new flows. See Update an existing Custom Connector.

Your existing flows will continue to work with the updated Custom Connector, so you don't need to update your flows too.

Set up the provided flows

We provide some example flows for email notifications:

• Purchase requisition flow for email notifications.
• Supplier bank details flow for email notifications.

Note: To set up these flows you will need your subscription key for the Sage API, and the site_id and company_id for each company. You can find your site_id and company_id when you run the GetSites test with your Custom Connector.

Note: We also provide some additional example flows. See Power Automate Demonstration Application (opens in a new tab).

Purchase requisitions flow (email notifications)

The purchase requisitions flow automatically sends emails to people to notify them during the authorisation process.

• Authorisers will receive email notifications when they have new purchase requisitions that require authorisation.
• The person who submitted the purchase requisition will receive an email notification when it is authorised or rejected.

This flow will run once per day and will send email notifications emails at 4pm (16:00), but you can configure the flow to change this frequency and timing.

Note: If you use multiple companies, the flow must be set up for each company.

Set up the flow

1. Go to make.powerautomate.com (opens in a new tab) and sign in the Microsoft 365 account you used to activate your subscription and link to Sage 200.
2. If you have more than one environment, check you're using the correct environment.
3. Select My flows.
4. Select Import.

5. Select Upload, then select the PurchaseRequisitionAuthorisationFlow.zip file that you downloaded.

6. Set the import options in Review Package Content.

   1. Purchase Requisition Authorisation Flow - Flow:

      1. Select the Action icon.
      2. Set Setup to Create as new.
      3. Select Save.

   2. Sage 200 Custom Connector - Connector:

      1. Select the Action icon.
      2. Select the name of your Custom Connector from the list (so it is ticked).
      3. Select Save.

   3. (email) - Sage 200 Custom Connector Connection:

      1. Select the Action icon.
      2. Select the email address of your Microsoft account from the list (so it is ticked).
      3. Select Save.

   4. (email) - Office 365 Outlook Connection:

      1. Select the Action icon.
      2. Select Create new. This will open a new page with a list of connections.
      3. Select New connection.

      4. Select Office 365 Outlook from the list, then select Create.

         Tip: To find this, type off in the Search box to filter the list.

      5. Go back to your flow, and select Refresh list to display the new connection.
      6. Select the connection from the list (so it is ticked).
      7. Select Save.

   5. Notifications - Notifications Connection: Select the Action icon, then select the email address for your Microsoft account from the list (so it is ticked). Select Save.

      1. Select the Action icon.
      2. Select Create new. This will open a new page with a list of connections.
      3. Select New connection.

      4. Select Notifications from the list, then select Create.

         Tip: To find this, type not in the Search box to filter the list.

      5. Go back to your flow, and select Refresh list to display the new connection.
      6. Select the connection from the list (so it is ticked).
      7. Select Save.

7. Once you've set up each import option, select Import to create the new flow.

   Note: You can only do this once each import option is complete.

8. Select My flows. The Purchase Requisition Authorisation Flow is displayed.

   Tip: If you can't see your flow, try refreshing your browser.

9. Select Edit (pencil icon) for the new Purchase Requisition Authorisation Flow.
   1. If you want to change the frequency of the flow to send email notifications, select Trigger. By default, the flow runs once per day at 4pm (16:00).

   2. Select Initialize SubscriptionKey, then set the Value to your subscription key for the Sage API.

   3. Select Initialize SiteID, then set the Value to the site_id for the Sage 200 company.

   4. Select Initialize CompanyID, then set the Value to the company_id for the Sage 200 company.

   5. Once you’ve entered the properties, select Save.

10. Check that your flow works:

    1. Go to My flows, and select Run for the flow.
    2. Select Run flow to start the flow.

    3. Once the flow is running, you can check the Run history for flow.

       If the flow is working, its Status will show Succeeded when it is complete.

       Tip: Try refreshing your browser to update the status.

Set up email addresses for users

Once you've set up the flow, enable notifications for each user:

Supplier bank details flow (email notifications)

You can send email notifications for changes to supplier bank details by using the supplier bank details flow.

The supplier bank details flow automatically sends emails to people to notify them of any changes to the bank details for supplier accounts. The emails are sent to everyone that has user access to the Audit Log, so that they can check the changes in the log.

This flow will run at 15 minute intervals each day, but you can configure the flow to change this frequency and timing.

Note: If you use multiple companies, the flow must be set up for each company.

Set up the flow

1. Go to make.powerautomate.com (opens in a new tab) and sign in using the Microsoft 365 account you used to activate your subscription and link to Sage 200.
2. If you have more than one environment, check you're using the correct environment.
3. Select My flows.
4. Select Import.

5. Select Upload, then select the SupplierBankDetailsFlow.zip file that you downloaded.

6. Set the import options in Review Package Content.

   1. SupplierBankDetailsFlow - Flow:

      1. Select the Action icon.
      2. Set Setup to Create as new.
      3. Select Save.

   2. Sage 200 Custom Connector - Connector:

      1. Select the Action icon.
      2. Select the name of your Custom Connector from the list (so it is ticked).
      3. Select Save.

   3. (email) - Sage 200 Custom Connector Connection:

      1. Select the Action icon.
      2. Select the email address of your Microsoft account from the list (so it is ticked).
      3. Select Save.

   4. (email) - Office 365 Outlook Connection:

      1. Select the Action icon.
      2. Select your Office 365 Outlook connection (if you've previously created a flow), or select Create new to add the connection.
      3. Select Save.

   5. Notifications - Notifications Connection: Select the Action icon, then select the email address for your Microsoft account from the list (so it is ticked). Select Save.

      1. Select the Action icon.
      2. Select your Notifications connection (if you've previously created a flow), or select Create new to add the connection.
      3. Select Save.

7. Once you've set up each import option, select Import to create the new flow.

   Note: You can only do this once each import option is complete.

8. Select My flows. The Supplier Bank Details Flow is displayed.

   Tip: If you can't see your flow, try refreshing your browser.

9. Select Edit (pencil icon) for the new Supplier Bank Details Flow.

   1. If you want to change the frequency of the flow to send email notifications, select Recurrence. By default, the flow runs every 15 minutes.

   2. Select Initialize SubscriptionKey, then set the Value to your subscription key for the Sage API.

   3. Select Initialize SiteID, then set the Value to the site_id for the Sage 200 company.

   4. Select Initialize CompanyID, then set the Value to the company_id for the Sage 200 company.

   5. Once you’ve entered the properties, select Save.

10. Check that your flow works:

    1. Go to My flows, and select Run for the flow.
    2. Select Run flow to start the flow.

    3. Once the flow is running, you can check the Run history for flow.

       If the flow is working, its Status will show Succeeded when it is complete.

       Tip: Try refreshing your browser to update the status.

You'll also need to set up email addresses for all users that will receive notifications. See Set up email addresses for users.